import os
import re


class DmTool:
    def __init__(self):
        pass

    def generate(self, table_script):

        # 去掉注释
        table_script = re.sub(
            "/\*.*?\*/", "", table_script, flags=re.DOTALL | re.MULTILINE
        )
        table_script = re.sub(
            "/\*.*?\*/;", "", table_script, flags=re.DOTALL | re.MULTILINE
        )
        # 去掉通过--后面所有的内容、
        table_script = re.sub(
            "--.*?\n", "", table_script, flags=re.DOTALL | re.MULTILINE
        )

        # 去掉drop table语句
        table_script = re.sub(
            "drop table if exists (.*?);", "", table_script, flags=re.IGNORECASE
        )
        # 去掉truncate table语句

        table_script = re.sub(
            "Table structure for (.*?)",
            "",
            table_script,
            flags=re.DOTALL | re.MULTILINE | re.IGNORECASE,
        )
        # 去掉SET相关的语句
        table_script = re.sub(
            r"^\s*SET\s+(.*?);", "", table_script, flags=re.IGNORECASE
        )
        db_name = re.findall(r"use (\w+)", table_script, flags=re.IGNORECASE)
        if not db_name:
            print("请指定上海数据库名，格式如下：use gwhome_user_sh;")
            return
        db = db_name[0]

        table_script = re.sub("use\s+(\w+);", "", table_script, flags=re.IGNORECASE)
        table_match = re.findall(
            r"CREATE TABLE (\w+)", table_script, flags=re.IGNORECASE | re.DOTALL
        )
        if not table_match:
            table_match = re.findall(
                r"CREATE TABLE `(\w+)`", table_script, flags=re.IGNORECASE | re.DOTALL
            )
        if not table_match:
            print("未获取到表名")
            return
        table_script = re.sub(r"`", '"', table_script)

        tables = re.split("create table", table_script, flags=re.IGNORECASE)
        print(f"use {db};")
        result_sql = ""
        for sc, tn in zip([tb for tb in tables if tb.strip()], table_match):
            table_name = tn
            result_sql += self.get_dm_script_from_mysql(
                "create table" + sc, db, table_name
            )

        return result_sql

    def get_dm_script_from_mysql(self, table_script, db, table_name):
        table_script = re.sub("/\*.*?\*/;", "", table_script, flags=re.IGNORECASE)
        table_script = re.sub(r"b '(\d)'", "b'\\1'", table_script, flags=re.IGNORECASE)
        table_script = re.sub(
            r"bigint\((\d+)\)", "bigint", table_script, flags=re.IGNORECASE
        )
        table_script = re.sub(r"unsigned", "", table_script, flags=re.IGNORECASE)
        table_script = re.sub(
            r"tinytext", "varchar(255)", table_script, flags=re.IGNORECASE
        )
        table_script = re.sub(r" json ", " CLOB ", table_script, flags=re.IGNORECASE)
        table_script = re.sub(r" zerofill ", " ", table_script, flags=re.IGNORECASE)
        table_script = re.sub(r"int\((\d+)\)", "int", table_script, flags=re.IGNORECASE)

        table_script = re.sub(
            r" char\((\d+)\)", " varchar2(\\1)", table_script, flags=re.IGNORECASE
        )

        table_script = re.sub(r"bit\(1\)", "bit", table_script, flags=re.IGNORECASE)
        table_script = re.sub(r"b'(\d)'", "'\\1'", table_script, flags=re.IGNORECASE)
        table_script = re.sub(
            r"tinyint\((\d+)\)", "tinyint", table_script, flags=re.IGNORECASE
        )
        table_script = re.sub(
            r"double|DOUBLE", "NUMERIC", table_script, flags=re.IGNORECASE
        )
        table_script = re.sub(
            r"longtext|mediumtext", "text", table_script, flags=re.IGNORECASE
        )
        table_script = re.sub(
            r"TABLE `(\w+)`", f'TABLE "{db}"."\\1"', table_script, flags=re.IGNORECASE
        )
        table_script = re.sub(
            r"EXISTS `(\w+)`", f'EXISTS "{db}"."\\1"', table_script, flags=re.IGNORECASE
        )
        table_script = re.sub(
            r"COLLATE utf8mb4_general_ci(\s+)",
            "",
            table_script,
            flags=re.IGNORECASE | re.MULTILINE,
        )
        table_script = re.sub(
            "BTREE COMMENT '(.*?)'", "BTREE", table_script, re.IGNORECASE
        )
        comments = re.findall(
            r"(?<!KEY) \"(\w+)\" .*? COMMENT '(.*?)'", table_script, flags=re.IGNORECASE
        )
        comments2 = re.findall(
            r"(\w+).*?COMMENT '(.*?)'", table_script, flags=re.IGNORECASE
        )
        if comments2:
            comments = comments2
        if not comments:
            comments = re.findall(
                r"(?<!KEY) (\w+) .*? COMMENT '(.*?)'", table_script, flags=re.IGNORECASE
            )
        if not comments:
            comments = re.findall(
                r"(?<!KEY) (\w+) .*? COMMENT '(.*?)'", table_script, flags=re.IGNORECASE
            )
        table_comments = re.findall(
            "COMMENT = '(.*?)';", table_script, flags=re.IGNORECASE
        )
        if not table_comments:
            table_comments = re.findall(
                "COMMENT\s+'(.*?)'\s+;", table_script, flags=re.IGNORECASE
            )

        table_script = re.sub(
            r"\s+CHARACTER SET (.*?) COLLATE (.*?)\s+",
            " ",
            table_script,
            flags=re.IGNORECASE | re.DOTALL | re.MULTILINE,
        )

        for i in range(3):
            table_script = re.sub(
                r"\s+comment\s+'(.*?)'",
                "",
                table_script,
                flags=re.IGNORECASE | re.DOTALL | re.DOTALL,
            )

        table_script = re.sub(
            r"\s+COMMENT\s+'(.*?)'",
            "",
            table_script,
            flags=re.IGNORECASE | re.DOTALL | re.DOTALL,
        )

        table_script = re.sub(
            r"SET character_set_client = utf8mb4 ;",
            "",
            table_script,
            flags=re.IGNORECASE,
        )
        table_script = re.sub(
            r"DEFAULT CHARSET = utf8mb4;", "", table_script, flags=re.IGNORECASE
        )
        table_script = re.sub(r"`", '"', table_script, flags=re.IGNORECASE)

        if re.search(r"PRIMARY KEY \((.*?)\)\s*,", table_script, flags=re.IGNORECASE):
            # print('contains...')
            table_script = re.sub(
                r"PRIMARY KEY \((.*?)\)\s*,",
                "NOT CLUSTER PRIMARY KEY (\\1)",
                table_script,
                flags=re.IGNORECASE,
            )
        else:
            table_script = re.sub(
                r"PRIMARY KEY \((.*?)\)",
                "NOT CLUSTER PRIMARY KEY (\\1)",
                table_script,
                flags=re.IGNORECASE,
            )
        # if re.search(r'PRIMARY KEY,', table_script):
        #     table_script = re.sub(r'PRIMARY KEY',
        #                           ',NOT CLUSTER PRIMARY KEY (id)', table_script,
        #                           flags=re.IGNORECASE | re.DOTALL | re.MULTILINE)
        # elif re.search(r'primary key,', table_script):
        #     table_script = re.sub(r'primary key',
        #                           ',NOT CLUSTER PRIMARY KEY (id)', table_script,
        #                           flags=re.IGNORECASE | re.DOTALL | re.MULTILINE)
        table_script = re.sub(
            r'UNIQUE KEY "(\w+)" \((.*?)\),',
            ",UNIQUE (\\2)",
            table_script,
            flags=re.IGNORECASE,
        )
        table_script = re.sub(
            r'UNIQUE KEY "(\w+)" \((.*?)\)',
            ",UNIQUE (\\2)",
            table_script,
            flags=re.IGNORECASE,
        )
        table_script = re.sub(
            r"unique key (\w+) \((.*?)\),",
            "UNIQUE (\\2)",
            table_script,
            flags=re.IGNORECASE,
        )
        table_script = re.sub(
            r"unique key (\w+) \((.*?)\)",
            "UNIQUE (\\2)",
            table_script,
            flags=re.IGNORECASE,
        )
        table_script = re.sub(
            "CURRENT_TIMESTAMP(?!\()",
            "CURRENT_TIMESTAMP(3)",
            table_script,
            flags=re.IGNORECASE,
        )
        table_script = re.sub(
            "\s+ON\s+UPDATE\s+CURRENT_TIMESTAMP\(3\)\s*",
            "",
            table_script,
            flags=re.IGNORECASE | re.DOTALL,
        )
        table_script = re.sub(
            "\s+on\s+update\s+CURRENT_TIMESTAMP\(3\)\s*",
            "",
            table_script,
            flags=re.IGNORECASE | re.DOTALL,
        )
        table_script = re.sub(
            "\s+ON\s+UPDATE\s+CURRENT_TIMESTAMP",
            "",
            table_script,
            flags=re.IGNORECASE | re.DOTALL,
        )
        table_script = re.sub(
            "\s+on\s+update\s+CURRENT_TIMESTAMP",
            "",
            table_script,
            flags=re.IGNORECASE | re.DOTALL,
        )
        table_script = re.sub(
            "= InnoDB", "", table_script, flags=re.IGNORECASE | re.DOTALL
        )

        varchar_all = re.findall("varchar\((\d+)\)", table_script, flags=re.IGNORECASE)
        # print(varchar_all)
        total_lenth = 0
        if varchar_all:
            total_lenth = sum([int(x) for x in varchar_all])
        if total_lenth > 2000:
            table_script = re.sub(
                "ENGINE=InnoDB DEFAULT CHAR",
                f'STORAGE(USING LONG ROW, ON "{db}", CLUSTERBTR);\n',
                table_script,
                flags=re.IGNORECASE,
            )
            table_script = re.sub(
                "ENGINE (.*?);",
                f'STORAGE(USING LONG ROW, ON "{db}", CLUSTERBTR);\n',
                table_script,
                flags=re.IGNORECASE,
            )
            table_script = re.sub(
                "ENGINE (.*?)",
                f'STORAGE(USING LONG ROW, ON "{db}", CLUSTERBTR);\n',
                table_script,
                flags=re.IGNORECASE,
            )
            table_script = re.sub(
                "ENGINE(.*?);",
                f'STORAGE(USING LONG ROW, ON "{db}", CLUSTERBTR);\n',
                table_script,
                flags=re.IGNORECASE,
            )
            table_script = re.sub(
                "ENGINE(.*?)",
                f'STORAGE(USING LONG ROW, ON "{db}", CLUSTERBTR);\n',
                table_script,
                flags=re.IGNORECASE,
            )
        else:
            table_script = re.sub(
                "ENGINE=InnoDB DEFAULT CHAR",
                f'STORAGE(ON "{db}", CLUSTERBTR);\n',
                table_script,
                flags=re.IGNORECASE,
            )
            table_script = re.sub(
                "ENGINE (.*?);",
                f'STORAGE(ON "{db}", CLUSTERBTR);\n',
                table_script,
                flags=re.IGNORECASE,
            )
            table_script = re.sub(
                "ENGINE (.*?)",
                f'STORAGE(ON "{db}", CLUSTERBTR);\n',
                table_script,
                flags=re.IGNORECASE,
            )
            table_script = re.sub(
                "ENGINE(.*?);",
                f'STORAGE(ON "{db}", CLUSTERBTR);\n',
                table_script,
                flags=re.IGNORECASE,
            )
            table_script = re.sub(
                "ENGINE(.*?)",
                f'STORAGE(ON "{db}", CLUSTERBTR);\n',
                table_script,
                flags=re.IGNORECASE,
            )

        table_script = re.sub(
            "USING BTREE,", f"", table_script, re.IGNORECASE | re.DOTALL | re.MULTILINE
        )
        table_script = re.sub(
            "USING BTREE", f"", table_script, re.IGNORECASE | re.DOTALL | re.MULTILINE
        )

        unique_indexes = re.findall(
            'UNIQUE INDEX "(.*?)" \("(.*?)"\)|KEY "(.*?)" \("(.*?)"\)',
            table_script,
            flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
        )
        indexes = re.findall(
            'INDEX "(.*?)" \("(.*?)"\)|KEY "(.*?)" \("(.*?)"\)',
            table_script,
            flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
        )

        if not indexes:
            indexes = re.findall(
                "INDEX (.*?)\s+on\s+\w+\s+\((.*?)\)",
                table_script,
                flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
            )
            unique_indexes = re.findall(
                "UNIQUE INDEX (.*?)\s+on\s+\w+\s+\((.*?)\)",
                table_script,
                flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
            )
        if not indexes:
            indexes = re.findall(
                "INDEX (.*?)\s*\((.*?)\)|KEY (.*?)\s*\((.*?)\)",
                table_script,
                flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
            )
            unique_indexes = re.findall(
                "UNIQUE INDEX (.*?)\s*\((.*?)\)|KEY (.*?)\s*\((.*?)\)",
                table_script,
                flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
            )
        if not indexes:
            indexes = re.findall(
                "INDEX (.*?)\s+on\s+\w+\s+\((.*?)\)",
                table_script,
                flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
            )
            unique_indexes = re.findall(
                "UNIQUE INDEX (.*?)\s+on\s+\w+\s+\((.*?)\)",
                table_script,
                flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
            )
        # print("indexes", indexes)
        table_script = re.sub(
            '(UNIQUE INDEX|INDEX)INDEX\s+"(.*?)"\s*\((.*?)\)(.*?),',
            "",
            table_script,
            flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
        )
        table_script = re.sub(
            '(UNIQUE INDEX|INDEX)\s+"(.*?)"\s*\((.*?)\)(.*?)',
            "",
            table_script,
            flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
        )

        table_script = re.sub(
            "CREATE\s+INDEX\s+(.*?)\s+?\((.*?)\);",
            "",
            table_script,
            flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
        )

        table_script = re.sub(
            r'KEY\s+"(.*?)"\s+\((.*?)\),',
            "",
            table_script,
            flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
        )
        table_script = re.sub(
            r"KEY\s+(.*?)\s+\((.*?)\),",
            "",
            table_script,
            flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
        )
        table_script = re.sub(
            r'KEY\s+"(.*?)"\s+\((.*?)\)',
            "",
            table_script,
            flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
        )
        table_script = re.sub(
            r"KEY\s+(.*?)\s+\((.*?)\)",
            "",
            table_script,
            flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
        )  # 删除单独的逗号行

        table_script = re.sub(
            r"INDEX\s+(.*?)\s*\((.*?)\),",
            "",
            table_script,
            flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
        )

        table_script = re.sub(
            r"INDEX\s+(.*?)\s*\((.*?)\)",
            "",
            table_script,
            flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
        )

        # table_script = re.sub(
        #     r'KEY \("id"\)', '', table_script, flags=re.IGNORECASE)
        # table_script = re.sub(r'NOT CLUSTER PRIMARY', 'NOT CLUSTER PRIMARY KEY (id)', table_script,
        #                       flags=re.MULTILINE | re.DOTALL | re.IGNORECASE)
        table_script = re.sub(
            r"KEY \(id\) KEY \(id\)",
            "KEY (id)",
            table_script,
            flags=re.MULTILINE | re.DOTALL | re.IGNORECASE,
        )
        if re.findall(f'("{table_name}")', table_script):
            table_script = re.sub(
                f'("{table_name}")',
                f'"{db}"."{table_name}"',
                table_script,
                flags=re.IGNORECASE,
            )
        else:
            table_script = re.sub(
                f"({table_name})",
                f'"{db}"."{table_name}"',
                table_script,
                flags=re.IGNORECASE,
            )
        table_script = re.sub(
            "constraint\s+(\w+)",
            f"constraint {table_name}_\\1",
            table_script,
            flags=re.IGNORECASE,
        )
        time_stamp = re.findall(
            "`(\w+)`.*?CURRENT_TIMESTAMP", table_script, flags=re.IGNORECASE
        )
        if not time_stamp:
            time_stamp = re.findall(
                "(\w+).*?CURRENT_TIMESTAMP", table_script, flags=re.IGNORECASE
            )
        time_stamp_field = ""
        if time_stamp:
            time_stamp_field = [t for t in time_stamp if t == "last_update_time"][0]
        if table_comments:
            table_script = (
                table_script
                + f'COMMENT ON TABLE "{db}"."{table_name}" IS \'{re.sub("&nbsp;", " ", table_comments[0])}\';\n'
            )
        for comment in comments:
            if comment[0] == "ENGINE" or comment[0] == "engine":
                table_script = (
                    table_script
                    + f'COMMENT ON TABLE "{db}"."{table_name}" IS \'{re.sub("&nbsp;", " ", comment[1])}\';\n'
                )
            else:
                table_script = (
                    table_script
                    + f'COMMENT ON COLUMN "{db}"."{table_name}"."{comment[0].upper()}" IS \'{re.sub("&nbsp;", " ", comment[1])}\';\n'
                )
        # 索引转换
        added_indexes = []
        for index in unique_indexes:
            print(index)
            if index[0]:
                index_name = re.sub(r'"', "", index[0])
                added_indexes.append(index_name)
                index_name1 = re.sub(r'"', "", index[1].upper())
                index_name1 = re.sub(r"ASC", "", index_name1, re.IGNORECASE)
                table_script = (
                    table_script
                    + f"drop index if exists {table_name}_{index_name};\nCREATE UNIQUE INDEX {table_name}_{index_name} ON {db}.{table_name}({index_name1} ASC) STORAGE(ON {db}, CLUSTERBTR);\n"
                )

            else:
                if index[2]:
                    index_name2 = re.sub(r'"', "", index[2])
                    added_indexes.append(index_name2)
                    index_name3 = re.sub(r'"', "", index[3].upper())
                    index_name3 = re.sub(r"ASC", "", index_name3, re.IGNORECASE)
                    table_script = (
                        table_script
                        + f"drop index if exists {table_name}_{index_name2};\nCREATE UNIQUE INDEX {table_name}_{index_name2} ON {db}.{table_name}({index_name3} ASC) STORAGE(ON {db}, CLUSTERBTR);\n"
                    )
        for index in indexes:
            print(index)
            if index[0]:
                index_name = re.sub(r'"', "", index[0])
                if index_name in added_indexes:
                    continue
                index_name1 = re.sub(r'"', "", index[1].upper())
                index_name1 = re.sub(r"ASC", "", index_name1, re.IGNORECASE)
                table_script = (
                    table_script
                    + f"drop index if exists {table_name}_{index_name};\nCREATE  INDEX {table_name}_{index_name} ON {db}.{table_name}({index_name1} ASC) STORAGE(ON {db}, CLUSTERBTR);\n"
                )

            else:
                if index[2]:
                    index_name2 = re.sub(r'"', "", index[2])
                    if index_name2 in added_indexes:
                        continue
                    index_name3 = re.sub(r'"', "", index[3].upper())
                    index_name3 = re.sub(r"ASC", "", index_name3, re.IGNORECASE)
                    table_script = (
                        table_script
                        + f"drop index if exists {table_name}_{index_name2};\nCREATE  INDEX {table_name}_{index_name2} ON {db}.{table_name}({index_name3} ASC) STORAGE(ON {db}, CLUSTERBTR);\n"
                    )

        if time_stamp_field:
            table_script = (
                table_script
                + f"""
create or replace trigger {table_name}_last_update_time_tg
before update on "{db}"."{table_name}" for each row
begin
:new.{time_stamp_field} =sysdate;
end;\n"""
            )

        table_script = re.sub(
            "^\n+$", "\n", table_script, flags=re.DOTALL | re.MULTILINE
        )
        # if not re.match(r'DROP', table_script, flags=re.IGNORECASE):
        #     table_script = f"DROP  TABLE IF EXISTS \"{db}\".\"{table_name}\";\n" + table_script
        table_script = re.sub(
            "USE (.*?);|use (.*?);", "", table_script, flags=re.IGNORECASE
        )
        table_script = re.sub(
            "CHARSET = utf8mb4;", "", table_script, flags=re.IGNORECASE
        )
        table_script = re.sub("CHARACTER", "", table_script, flags=re.IGNORECASE)
        # 删除掉空行
        table_script = re.sub(
            "\n\s*\n", "\n", table_script, flags=re.DOTALL | re.MULTILINE
        )
        print(table_script)
        return table_script

# @extism.plugin_fn
def hello_world():
    params = extism.input_json()
    name = params["name"]
    age = params["age"]
    if not name:
        name = "World"
    extism.output_str(f"Hello {name}，I am {age} years old!")


# @extism.plugin_fn
def generate_dm_script(db, sql_dir):
    dm_tool = DmTool()
    if db:
        db = f"use {db}"

    print(db)
    print(sql_dir)
    sql_files = [x for x in os.listdir(sql_dir) if x.endswith(".sql")]
    if not os.path.exists(os.path.join(sql_dir, "dm")):
        os.mkdir(os.path.join(sql_dir, "dm"))
    for sql_file in sql_files:
        with open(
            os.path.join(sql_dir, sql_file), "r", encoding="utf-8-sig"
        ) as f:  # Python 的 utf-8-sig 编码会自动跳过 BOM 字符
            sql_script = f.read()
            table_script = f"""
        {db};
        {sql_script}
            """
            result = dm_tool.generate(table_script)
            if result:
                with open(
                    os.path.join(sql_dir, "dm", f"{sql_file.split('.')[0]}.dm.sql"),
                    "w",
                    encoding="utf-8",
                ) as f:
                    f.write(result)
            return result

    return "success"
